//
//  YYT_DataBaseSingleton.m
//  AudioVisualFeast
//
//  Created by 张琪 on 16/2/27.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "QL_DataBaseSingleton.h"
#import <sqlite3.h>
@implementation QL_DataBaseSingleton
+ (instancetype)shareDataBase
{
    static QL_DataBaseSingleton *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[QL_DataBaseSingleton alloc]init];
    }
    return dataBase;
}

static sqlite3 *db = nil;
- (void)openDB{
    if (db != nil) {
        NSLog(@"数据库已经打开了");
        return;
    }
    //拼接文件路径
    NSString *documentPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *filePath = [documentPath stringByAppendingPathComponent:@"history.sqlite"];
    NSLog(@"filePath = %@",filePath);
    BOOL result = sqlite3_open(filePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功");
    }else{
        NSLog(@"数据库打开失败");
    }
    
}


- (void)closeDB{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功");
    }else{
        NSLog(@"数据库关闭失败");
    }
}

- (void)creatTable{
    NSString *sqlStr = @"create table if not exists history(id integer primary key autoincrement not null,ids text not null,title text not null,imageurl text not null)";
    int result = sqlite3_exec(db, sqlStr.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}

- (void)insertDB:(NSString *)ids withTitle:(NSString *)title withImageUrl:(NSString *)imageUrl{
    //    1.准备sql语句
    NSString *sqlString = @"insert into history(ids,title,imageurl) values (?,?,?)";
    //    2.获取一个伴随指针 （绑定参数，获取数据） -  数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //    3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        4.绑定参数
        sqlite3_bind_text(stmt, 1, ids.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, imageUrl.UTF8String, -1, NULL);
        //        5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入成功");
        } else {
            NSLog(@"插入失败");
        }
    } else {
        NSLog(@"语句错误");
    }
    //    6.关闭伴随指针
    sqlite3_finalize(stmt);
}

//查询数据
- (NSString *)selectDataBase:(NSString *)ids {
    //    1.准备sql语句
    NSString *sqlString = @"select ids,title,imageurl from history where ids = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, ids.UTF8String, -1, NULL);
        //        执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据
            NSString *selectIds = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            sqlite3_finalize(stmt);
            return selectIds;
        }
        
    } else {
        NSLog(@"语句错误");
    }
    sqlite3_finalize(stmt);
    return nil;
}

//查询数据 返回一个数组
-(NSMutableArray *)selectDataBasearray {
    NSMutableArray *selIds = [[NSMutableArray alloc] initWithCapacity:0];
    NSMutableArray *selTitle = [[NSMutableArray alloc] initWithCapacity:0];
    NSMutableArray *selImageurl = [[NSMutableArray alloc] initWithCapacity:0];
    //    1.准备sql语句
    NSString *sqlString = @"select ids,title,imageurl from history";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据
            NSString *selectIds = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            [selIds addObject:selectIds];
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            [selTitle addObject:selectTitle];
            NSString *selectImageurl = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            [selImageurl addObject:selectImageurl];
        }
        NSMutableArray *array = [[NSMutableArray alloc] initWithObjects:selIds, selTitle, selImageurl, nil];
        sqlite3_finalize(stmt);
        return array;
    } else {
        NSLog(@"语句错误");
    }
    sqlite3_finalize(stmt);
    return nil;
}

- (NSMutableArray *)chaxunseebiao{
    NSMutableArray *array = [NSMutableArray array];
    
    //查询指定条件的
    NSString *sql = @"SELECT * FROM see";
    
    //查询所有
    //    NSString *sql = @"SELECT * FROM lanou1029";
    
    //创建数据库跟随指针,主要是用于遍历查询表中每一行上的数据.
    sqlite3_stmt *stmt = nil;
    //准备查询工作
    //参数3:-1代表sql语句不限长度
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        
        NSLog(@"查询准备成功");
    }else
    {
        NSLog(@"准备失败");
        return nil;
    }
    
    //循环遍历每一行
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        NSString *selectIds = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
        [array addObject:selectIds];
    }
    return array;

}

//删除
-(void)deleteSQL:(NSString *)ids {
    NSString *sqlString = @"delete from history where ids = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, ids.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除成功");
        } else {
            NSLog(@"删除失败");
        }
    } else {
        NSLog(@"语句错误");
    }
    sqlite3_finalize(stmt);
}

//创建表
-(void)createSeeTable {
    //    1.准备数据库语句
    NSString *sqlString = @"create table if not exists see(id integer primary key autoincrement not null,ids text not null,title text not null,imageurl text not null)";
    //    2.执行sql语句,
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"建观看历史表成功");
    } else {
        NSLog(@"建表失败");
    }
}

//插入数据
-(void)insertSeeDataBase:(NSString *)ids withTitle:(NSString *)title withImageurl:(NSString *)imageurl {
    //    1.准备sql语句
    NSString *sqlString = @"insert into see(ids,title,imageurl) values (?,?,?)";
    //    2.获取一个伴随指针 （绑定参数，获取数据） -  数据库的状态指针
    sqlite3_stmt *stmt = NULL;
    //    3.预执行
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        4.绑定参数
        sqlite3_bind_text(stmt, 1, ids.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 2, title.UTF8String, -1, NULL);
        sqlite3_bind_text(stmt, 3, imageurl.UTF8String, -1, NULL);
        //        5.执行
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"插入观看表成功");
        } else {
            NSLog(@"插入失败");
        }
    } else {
        NSLog(@"语句错误");
    }
    
    //    6.关闭伴随指针
    sqlite3_finalize(stmt);
}

//查询数据
- (NSString *)selectSeeDataBase:(NSString *)ids {
    //    1.准备sql语句
    NSString *sqlString = @"select ids,title,imageurl from  where ids = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, ids.UTF8String, -1, NULL);
        //        执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据
            NSString *selectIds = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            sqlite3_finalize(stmt);
            return selectIds;
        }
        
    } else {
        NSLog(@"语句错误");
    }
    sqlite3_finalize(stmt);
    return nil;
}

//查询数据 返回一个数组
-(NSMutableArray *)selectSeeDataBasearray {
    NSMutableArray *selIds = [[NSMutableArray alloc] initWithCapacity:0];
    NSMutableArray *selTitle = [[NSMutableArray alloc] initWithCapacity:0];
    NSMutableArray *selImageurl = [[NSMutableArray alloc] initWithCapacity:0];
    //    1.准备sql语句
    NSString *sqlString = @"select ids,title,imageurl from see";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        //        执行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //            从伴随指针获取数据
            NSString *selectIds = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 0)];
            [selIds addObject:selectIds];
            NSString *selectTitle = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 1)];
            [selTitle addObject:selectTitle];
            NSString *selectImageurl = [NSString stringWithUTF8String:(const char *)sqlite3_column_text(stmt, 2)];
            [selImageurl addObject:selectImageurl];
        }
        NSMutableArray *array = [[NSMutableArray alloc] initWithObjects:selIds, selTitle, selImageurl, nil];
        sqlite3_finalize(stmt);
        return array;
    } else {
        NSLog(@"语句错误");
    }
    sqlite3_finalize(stmt);
    return nil;
}


//删除
-(void)deleteSeeSQL:(NSString *)ids {
    NSString *sqlString = @"delete from see where ids = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare(db, sqlString.UTF8String, -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, ids.UTF8String, -1, NULL);
        if (sqlite3_step(stmt) == SQLITE_DONE) {
            NSLog(@"删除成功");
        } else {
            NSLog(@"删除失败");
        }
    } else {
        NSLog(@"语句错误");
    }
    sqlite3_finalize(stmt);
}

- (void)deleteTable {
    NSString *sqlString = @"DROP table history";
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"收藏表删除成功");
    }
    else
    {
        NSLog(@"收藏表删除失败");
    }
    
}

- (void)deleteSeeTable {
    NSString *sqlString = @"DROP table see";
    int result = sqlite3_exec(db, sqlString.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"历史表删除成功");
    }
    else
    {
        NSLog(@"历史表删除失败");
    }
    
}

@end
